package nl.ruudshuis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import nl.ruudshuis.beans.BiedingBean;
import nl.ruudshuis.beans.GebruikerBean;
import nl.ruudshuis.beans.VeilingBean;
import nl.ruudshuis.db.DBmanager;

public class BiedingDao {

	private String sqlBiedingread = "SELECT userId, prijs from BIEDING WHERE veilingId = ? ORDER BY prijs DESC";
	private String insert = "INSERT INTO BIEDING (veilingId, userId, prijs) VALUES ( ?, ?, ?)";
	private String mijnBiedingen = "SELECT * FROM BIEDING JOIN VEILING ON BIEDING.veilingId = VEILING.veilingId WHERE userId = ? ORDER BY VEILING.eindtijd DESC";
	private String laatsteBodVeiling = "SELECT prijs FROM BIEDING WHERE veilingId = ? ORDER BY prijs DESC";
	private String checkAlGeboden = "SELECT * FROM BIEDING WHERE userId = ? AND veilingId = ?";
	private String updateBieding = "UPDATE BIEDING SET prijs = ? WHERE userId = ? AND veilingId = ?";
	private DBmanager myDb;
	
	private Connection con = null;
	private PreparedStatement psRead = null;
	private PreparedStatement psInsert = null;
	private PreparedStatement psMijnBiedingen = null;
	private PreparedStatement psLaatsteBod = null;
	private PreparedStatement psCheck = null;
	private PreparedStatement psUpdate = null;

	public BiedingDao(DBmanager db) {
		myDb = db;
		con = myDb.getConnection();
		try {
			this.psRead = con.prepareStatement(sqlBiedingread);
			this.psInsert = con.prepareStatement(insert);
			this.psMijnBiedingen = con.prepareStatement(mijnBiedingen);
			this.psLaatsteBod = con.prepareStatement(laatsteBodVeiling);
			this.psCheck = con.prepareStatement(checkAlGeboden);
			this.psUpdate = con.prepareStatement(updateBieding);

		} catch (SQLException se) {
			printSQLException(se);
			se.printStackTrace();
		}
	}
	
	public boolean checkAlGeboden(int userId, int veilingId){
		boolean geboden = false;
		
		try {
			psCheck.clearParameters();

			psCheck.setInt(1, userId);
			psCheck.setInt(2, veilingId);
		
			int rowCount = 0;
			ResultSet rs = psCheck.executeQuery();
			while(rs.next()){
				rowCount++;
			}
		    
		    if(rowCount >= 1){
		    	geboden = true;
		    }
			
		} catch (SQLException se) {
			printSQLException(se);
		}
		return geboden;
	}
	
	public void createBieding(int veilingId, int userId, double prijs) {

		try {
			
			if(!checkAlGeboden(userId, veilingId)){
				psInsert.clearParameters();

				psInsert.setInt(1, veilingId);
				psInsert.setInt(2, userId);
				psInsert.setDouble(3, prijs);
			
				int resultaat = psInsert.executeUpdate();
				System.out.println("RESULTAAT: "+ resultaat);
				if (resultaat == 0) {
					System.out.println("nix done");
				}
			}
			else{
				psUpdate.clearParameters();
				
				psUpdate.setDouble(1, prijs);
				psUpdate.setInt(2, userId);
				psUpdate.setInt(3, veilingId);
				
				int resultaat = psUpdate.executeUpdate();
				System.out.println("RESULTAAT: "+ resultaat);
				if (resultaat == 0) {
					System.out.println("nix done");
				}
			}
			
		} catch (SQLException se) {
			printSQLException(se);
		}
	}

	public LinkedList<BiedingBean> getBiedingen(int veilingId) {

		LinkedList<BiedingBean> biedingen = new LinkedList<BiedingBean>();

		try {
			psRead.setInt(1, veilingId);
			ResultSet rs = psRead.executeQuery();
			
			GebruikersDao gd = new GebruikersDao(myDb);
			VeilingDao vd = new VeilingDao(myDb);
			VeilingBean v = vd.getVeilingOpId(veilingId);

			while (rs.next()) {
				int userId = rs.getInt("userId");
				double prijs = rs.getDouble("prijs");
				
				GebruikerBean g = gd.getGebruikerOpID(userId);
				BiedingBean b = new BiedingBean();
			    b.setAll(v, g, prijs);
				biedingen.add(b);
			}

			rs.close();

		} catch (SQLException se) {
			printSQLException(se);
		}

		return biedingen;
	}
	
	public LinkedList<BiedingBean> getMijnBiedingen(int userId) {
		LinkedList<BiedingBean> biedingen = new LinkedList<BiedingBean>();			
		BiedingBean b = null;
		VeilingDao v = new VeilingDao(myDb);
		GebruikersDao g = new GebruikersDao(myDb);
		try {
			psMijnBiedingen.setInt(1, userId);
			ResultSet rs = psMijnBiedingen.executeQuery();

			while (rs.next()) {
				int veilingId = rs.getInt("veilingId");
				double prijs = rs.getDouble("prijs");
				
				b = new BiedingBean();
				b.setAll(v.getVeilingOpId(veilingId), g.getGebruikerOpID(userId), prijs);
				biedingen.add(b);
			}

			rs.close();

		} catch (SQLException se) {
			printSQLException(se);
		}

		return biedingen;
	}
	
	public double getLaatsteBod(int veilingId){
		double prijs = 0.0;
		try{
			psLaatsteBod.clearParameters();
			psLaatsteBod.setInt(1, veilingId);
			psLaatsteBod.setMaxRows(1);
			ResultSet rs = psLaatsteBod.executeQuery();
			
			while (rs.next()) {
				prijs = rs.getDouble("prijs");
			}
			rs.close();

		} catch (SQLException se) {
			printSQLException(se);
		}
		
		if(prijs == 0.0){
			VeilingDao v = new VeilingDao(myDb);
			prijs = v.getPrijsVanVeiling(veilingId);
		}

		return prijs;
	}

	private void printSQLException(SQLException se) {
		while (se != null) {

			System.out.print("SQLException: State:   " + se.getSQLState());
			System.out.println("Severity: " + se.getErrorCode());
			System.out.println(se.getMessage());

			se = se.getNextException();
		}
	}
}
